/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLager;

import ModelLager.InVoiceP;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author JesperJensen
 */
public class DbInVoiceP implements IFDbInVoiceP
{
    private Connection con;
    PreparedStatement pStmtSelect;
    //private PreparedStatement pStmtSelectAllInvoice;
    
    public DbInVoiceP()
    {
        con = DbConnection2.getInstance().getDBcon();
    }
    
    public InVoiceP findInVoiceP(int InVoiceNo)
    {
        InVoiceP InVoiceObj = new InVoiceP();
        InVoiceObj = findWithPreQuery(InVoiceNo);
        return InVoiceObj;
        
    }
    
    public int insertInvoice(InVoiceP InvObj)
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String insert = "INSERT INTO InVoiceP(paymentDate, amount, InVoiceNo)"
                                             + "VALUES (?,?,?)";
        System.out.println(insert);
        try
        {
            pstmt = con.prepareStatement(insert);
            pstmt.setString(1, InvObj.getPaymentDate());
            pstmt.setInt(2, InvObj.getAmount());
            pstmt.setInt(3, InvObj.getInVoiceNo());
            
            rc = pstmt.executeUpdate();
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            e.getMessage();
        }
        return rc;
    }
    
    public int deleteInVoiceP(int inVoiceNo)
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String delete = "delete from InVoiceP where inVoiceNo = ?";
        System.out.println(delete);
        try
        {
             pstmt = con.prepareStatement(delete);
             pstmt.setInt(1,inVoiceNo);
             rc = pstmt.executeUpdate();                       
        }
        catch(SQLException sqlE)
        {
             System.out.println("SQL Error");
             System.out.println(sqlE.getMessage());   
        }
        catch(Exception e)
        {
             e.getMessage();
                 
        }
        return rc;
    }
    
    
    private InVoiceP buildInVoiceP(ResultSet results)
    {
        InVoiceP InVoiceObj = new InVoiceP();
        try
        {   
            InVoiceObj.setPaymentDate(results.getString("paymentDate"));
            InVoiceObj.setAmount(results.getInt("amount"));
            InVoiceObj.setInVoiceNo(results.getInt("inVoiceNo"));
        }
        catch(Exception e)
        {
            System.out.println("Error in Building inVoiceP Object");
        }
        return InVoiceObj;
    }
    
    public InVoiceP findWithPreQuery(int inVoiceNo)
    {
        System.out.println("Find With preCompiled");
        String wClause = "InVoiceNo = ?";
        if(pStmtSelect == null)
            {
                pStmtSelect = getPreparedStmt(wClause);
            }
        InVoiceP InVoiceObj = null;
        ResultSet results;
        try
            {
                pStmtSelect.setInt(1, inVoiceNo);
                results = pStmtSelect.executeQuery();
                if(results.next())
                    {
                        InVoiceObj = buildInVoiceP(results);
                    }
            }
        catch(Exception e)
            {
                e.getMessage();
            }
        return InVoiceObj;
    }
    
    private PreparedStatement getPreparedStmt(String wClause)
    {
        try
        {
            String query = " SELECT * "
                    + "FROM InVoiceP ";
            if(wClause.length()>0)
                    {
                        query = query + "WHERE " + wClause;
                    }
            
            System.out.println("PreparedStatement" + query);
            pStmtSelect = con.prepareStatement(query);
            pStmtSelect.setQueryTimeout(5);
        }
        catch(Exception e)
        {
            e.getMessage();
        }
        return pStmtSelect;
    }
    
    public ArrayList<InVoiceP> findByAllInVoicePreQuery()
    {
        ResultSet results;
        ArrayList<InVoiceP> list = new ArrayList<InVoiceP>();
        
        try
            {
                pStmtSelect = getPreparedStmt("");
                results = pStmtSelect.executeQuery();
                
                pStmtSelect.setQueryTimeout(5);
                
            
            while (results.next())
                {
                    InVoiceP InvoiceObj = new InVoiceP();
                    InvoiceObj = buildInVoiceP(results);
                    list.add(InvoiceObj);                    
                }//end while
                pStmtSelect.close();
            }
        
        catch(Exception e)
                {
                    e.getMessage();
                }
        
        return list;
    }
}
